using Microsoft.Data.SqlClient;
using DatabaseManager.Models;

namespace DatabaseManager.Services;

/// <summary>
/// 数据库管理服务
/// </summary>
public class DatabaseManagementService
{
    private readonly string _connectionString;

    public DatabaseManagementService(string connectionString)
    {
        _connectionString = connectionString;
    }

    #region 账号管理

    /// <summary>
    /// 获取所有账号
    /// </summary>
    public async Task<List<DatabaseAccount>> GetAllAccountsAsync()
    {
        var accounts = new List<DatabaseAccount>();

        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT 
                    name AS LoginName,
                    type_desc AS AccountType,
                    create_date AS CreateDate,
                    modify_date AS ModifyDate,
                    default_database_name AS DefaultDatabase,
                    is_disabled AS IsDisabled
                FROM sys.server_principals
                WHERE type IN ('S', 'U') -- S=SQL登录, U=Windows登录
                AND name NOT LIKE '##%' -- 排除系统账号
                AND name NOT IN ('sa', 'guest')
                ORDER BY name";

            using var command = new SqlCommand(query, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                accounts.Add(new DatabaseAccount
                {
                    LoginName = reader.GetString(0),
                    AccountType = reader.GetString(1),
                    CreateDate = reader.IsDBNull(2) ? null : reader.GetDateTime(2),
                    ModifyDate = reader.IsDBNull(3) ? null : reader.GetDateTime(3),
                    DefaultDatabase = reader.GetString(4),
                    IsDisabled = reader.GetBoolean(5),
                    Status = reader.GetBoolean(5) ? "已禁用" : "正常"
                });
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"数据库连接失败: {ex.Message}", ex);
        }

        return accounts;
    }

    /// <summary>
    /// 获取账号详情
    /// </summary>
    public async Task<DatabaseAccount?> GetAccountAsync(string loginName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT 
                    name AS LoginName,
                    type_desc AS AccountType,
                    create_date AS CreateDate,
                    modify_date AS ModifyDate,
                    default_database_name AS DefaultDatabase,
                    is_disabled AS IsDisabled
                FROM sys.server_principals
                WHERE name = @LoginName";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@LoginName", loginName);

            using var reader = await command.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                return new DatabaseAccount
                {
                    LoginName = reader.GetString(0),
                    AccountType = reader.GetString(1),
                    CreateDate = reader.IsDBNull(2) ? null : reader.GetDateTime(2),
                    ModifyDate = reader.IsDBNull(3) ? null : reader.GetDateTime(3),
                    DefaultDatabase = reader.GetString(4),
                    IsDisabled = reader.GetBoolean(5),
                    Status = reader.GetBoolean(5) ? "已禁用" : "正常"
                };
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取账号详情失败: {ex.Message}", ex);
        }

        return null;
    }

    /// <summary>
    /// 创建账号
    /// </summary>
    public async Task<bool> CreateAccountAsync(CreateAccountRequest request)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 创建登录 - 注意：密码不能使用参数化查询，需要特殊处理
            // 为安全起见，我们需要确保密码不包含恶意字符
            if (string.IsNullOrEmpty(request.Password) || request.Password.Contains("'"))
            {
                throw new ArgumentException("密码不能为空且不能包含单引号");
            }

            var createLoginSql = $@"
                CREATE LOGIN [{request.LoginName}] 
                WITH PASSWORD = '{request.Password.Replace("'", "''")}',
                DEFAULT_DATABASE = [{request.DefaultDatabase}],
                CHECK_EXPIRATION = OFF,
                CHECK_POLICY = OFF";

            using var command = new SqlCommand(createLoginSql, connection);
            await command.ExecuteNonQueryAsync();

            // 如果提供了描述，添加扩展属性
            if (!string.IsNullOrEmpty(request.Description))
            {
                var addDescriptionSql = @"
                    EXEC sys.sp_addextendedproperty 
                    @name = N'MS_Description',
                    @value = @Description,
                    @level0type = N'LOGIN',
                    @level0name = @LoginName";

                using var descCommand = new SqlCommand(addDescriptionSql, connection);
                descCommand.Parameters.AddWithValue("@Description", request.Description);
                descCommand.Parameters.AddWithValue("@LoginName", request.LoginName);

                try
                {
                    await descCommand.ExecuteNonQueryAsync();
                }
                catch
                {
                    // 忽略扩展属性错误
                }
            }

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"创建账号失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 删除账号
    /// </summary>
    public async Task<bool> DeleteAccountAsync(string loginName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 删除登录
            var dropLoginSql = "DROP LOGIN [@LoginName]";

            using var command = new SqlCommand(dropLoginSql, connection);
            command.Parameters.AddWithValue("@LoginName", loginName);
            
            await command.ExecuteNonQueryAsync();

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"删除账号失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 修改密码
    /// </summary>
    public async Task<bool> ChangePasswordAsync(ChangePasswordRequest request)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 修改密码 - 注意：新密码不能使用参数化查询
            if (string.IsNullOrEmpty(request.NewPassword) || request.NewPassword.Contains("'"))
            {
                throw new ArgumentException("新密码不能为空且不能包含单引号");
            }

            var alterLoginSql = $@"
                ALTER LOGIN [{request.LoginName}] 
                WITH PASSWORD = '{request.NewPassword.Replace("'", "''")}'";

            using var command = new SqlCommand(alterLoginSql, connection);
            await command.ExecuteNonQueryAsync();

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"修改密码失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 启用/禁用账号
    /// </summary>
    public async Task<bool> ToggleAccountStatusAsync(string loginName, bool disable)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var alterLoginSql = @"
                ALTER LOGIN [@LoginName] 
                @Action";

            using var command = new SqlCommand(alterLoginSql, connection);
            command.Parameters.AddWithValue("@LoginName", loginName);
            command.Parameters.AddWithValue("@Action", disable ? "DISABLE" : "ENABLE");
            
            await command.ExecuteNonQueryAsync();

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"修改账号状态失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 检查账号是否存在
    /// </summary>
    public async Task<bool> AccountExistsAsync(string loginName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = "SELECT COUNT(1) FROM sys.server_principals WHERE name = @LoginName";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@LoginName", loginName);

            var count = (int)await command.ExecuteScalarAsync();
            return count > 0;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"检查账号存在性失败: {ex.Message}", ex);
        }
    }

    #endregion

    #region 数据库管理

    /// <summary>
    /// 获取所有数据库
    /// </summary>
    public async Task<List<DatabaseInfo>> GetAllDatabasesAsync()
    {
        var databases = new List<DatabaseInfo>();

        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT 
                    d.name AS DatabaseName,
                    d.database_id AS DatabaseId,
                    d.create_date AS CreateDate,
                    SUSER_SNAME(d.owner_sid) AS Owner,
                    d.state_desc AS State,
                    d.recovery_model_desc AS RecoveryModel,
                    d.compatibility_level AS CompatibilityLevel,
                    d.collation_name AS Collation,
                    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
                FROM sys.databases d
                LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
                WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
                GROUP BY d.name, d.database_id, d.create_date, d.owner_sid, 
                         d.state_desc, d.recovery_model_desc, d.compatibility_level, d.collation_name
                ORDER BY d.name";

            using var command = new SqlCommand(query, connection);
            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                databases.Add(new DatabaseInfo
                {
                    DatabaseName = reader.GetString(0),
                    DatabaseId = reader.GetInt32(1),
                    CreateDate = reader.IsDBNull(2) ? null : reader.GetDateTime(2),
                    Owner = reader.IsDBNull(3) ? "" : reader.GetString(3),
                    State = reader.GetString(4),
                    RecoveryModel = reader.GetString(5),
                    CompatibilityLevel = reader.GetByte(6),
                    Collation = reader.IsDBNull(7) ? "" : reader.GetString(7),
                    SizeMB = reader.IsDBNull(8) ? 0 : reader.GetDecimal(8)
                });
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取数据库列表失败: {ex.Message}", ex);
        }

        return databases;
    }

    /// <summary>
    /// 获取数据库详情
    /// </summary>
    public async Task<DatabaseInfo?> GetDatabaseAsync(string databaseName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT 
                    d.name AS DatabaseName,
                    d.database_id AS DatabaseId,
                    d.create_date AS CreateDate,
                    SUSER_SNAME(d.owner_sid) AS Owner,
                    d.state_desc AS State,
                    d.recovery_model_desc AS RecoveryModel,
                    d.compatibility_level AS CompatibilityLevel,
                    d.collation_name AS Collation,
                    CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
                FROM sys.databases d
                LEFT JOIN sys.master_files mf ON d.database_id = mf.database_id
                WHERE d.name = @DatabaseName
                GROUP BY d.name, d.database_id, d.create_date, d.owner_sid, 
                         d.state_desc, d.recovery_model_desc, d.compatibility_level, d.collation_name";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@DatabaseName", databaseName);

            using var reader = await command.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                return new DatabaseInfo
                {
                    DatabaseName = reader.GetString(0),
                    DatabaseId = reader.GetInt32(1),
                    CreateDate = reader.IsDBNull(2) ? null : reader.GetDateTime(2),
                    Owner = reader.IsDBNull(3) ? "" : reader.GetString(3),
                    State = reader.GetString(4),
                    RecoveryModel = reader.GetString(5),
                    CompatibilityLevel = reader.GetByte(6),
                    Collation = reader.IsDBNull(7) ? "" : reader.GetString(7),
                    SizeMB = reader.IsDBNull(8) ? 0 : reader.GetDecimal(8)
                };
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取数据库详情失败: {ex.Message}", ex);
        }

        return null;
    }

    /// <summary>
    /// 创建数据库
    /// </summary>
    public async Task<bool> CreateDatabaseAsync(CreateDatabaseRequest request)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 创建数据库
            var createDbSql = @"
                CREATE DATABASE [@DatabaseName]
                COLLATE @Collation";

            using var command = new SqlCommand(createDbSql, connection);
            command.Parameters.AddWithValue("@DatabaseName", request.DatabaseName);
            command.Parameters.AddWithValue("@Collation", request.Collation);
            await command.ExecuteNonQueryAsync();

            // 设置恢复模式
            var alterDbSql = @"
                ALTER DATABASE [@DatabaseName] 
                SET RECOVERY @RecoveryModel";

            using var alterCommand = new SqlCommand(alterDbSql, connection);
            alterCommand.Parameters.AddWithValue("@DatabaseName", request.DatabaseName);
            alterCommand.Parameters.AddWithValue("@RecoveryModel", request.RecoveryModel);
            await alterCommand.ExecuteNonQueryAsync();

            // 设置所有者（如果指定）
            if (!string.IsNullOrEmpty(request.Owner))
            {
                var setOwnerSql = @"
                    ALTER AUTHORIZATION ON DATABASE::[@DatabaseName] 
                    TO [@Owner]";

                using var ownerCommand = new SqlCommand(setOwnerSql, connection);
                ownerCommand.Parameters.AddWithValue("@DatabaseName", request.DatabaseName);
                ownerCommand.Parameters.AddWithValue("@Owner", request.Owner);
                await ownerCommand.ExecuteNonQueryAsync();
            }

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"创建数据库失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 删除数据库
    /// </summary>
    public async Task<bool> DeleteDatabaseAsync(string databaseName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 设置为单用户模式，强制断开所有连接
            var setSingleUserSql = @"
                ALTER DATABASE [@DatabaseName] 
                SET SINGLE_USER WITH ROLLBACK IMMEDIATE";

            using var singleUserCommand = new SqlCommand(setSingleUserSql, connection);
            singleUserCommand.Parameters.AddWithValue("@DatabaseName", databaseName);
            await singleUserCommand.ExecuteNonQueryAsync();

            // 删除数据库
            var dropDbSql = "DROP DATABASE [@DatabaseName]";

            using var dropCommand = new SqlCommand(dropDbSql, connection);
            dropCommand.Parameters.AddWithValue("@DatabaseName", databaseName);
            await dropCommand.ExecuteNonQueryAsync();

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"删除数据库失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 检查数据库是否存在
    /// </summary>
    public async Task<bool> DatabaseExistsAsync(string databaseName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = "SELECT COUNT(1) FROM sys.databases WHERE name = @DatabaseName";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@DatabaseName", databaseName);

            var count = (int)await command.ExecuteScalarAsync();
            return count > 0;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"检查数据库存在性失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 获取数据库大小
    /// </summary>
    public async Task<decimal> GetDatabaseSizeAsync(string databaseName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
                FROM sys.master_files
                WHERE database_id = DB_ID(@DatabaseName)";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@DatabaseName", databaseName);

            var result = await command.ExecuteScalarAsync();
            return result != DBNull.Value ? (decimal)result : 0;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取数据库大小失败: {ex.Message}", ex);
        }
    }

    #endregion

    #region 权限管理

    /// <summary>
    /// 获取账号的数据库权限
    /// </summary>
    public async Task<List<DatabasePermission>> GetAccountPermissionsAsync(string loginName, string? databaseName = null)
    {
        var permissions = new List<DatabasePermission>();

        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 获取所有数据库
            var databases = new List<string>();
            var dbQuery = databaseName != null 
                ? "SELECT name FROM sys.databases WHERE name = @DatabaseName"
                : "SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')";

            using (var dbCommand = new SqlCommand(dbQuery, connection))
            {
                if (databaseName != null)
                {
                    dbCommand.Parameters.AddWithValue("@DatabaseName", databaseName);
                }

                using var dbReader = await dbCommand.ExecuteReaderAsync();
                while (await dbReader.ReadAsync())
                {
                    databases.Add(dbReader.GetString(0));
                }
            }

            // 遍历每个数据库获取权限
            foreach (var db in databases)
            {
                try
                {
                    var permQuery = $@"
                        USE [{db}];
                        SELECT 
                            @LoginName AS PrincipalName,
                            @DatabaseName AS DatabaseName,
                            p.permission_name AS PermissionType,
                            p.state_desc AS PermissionState,
                            o.type_desc AS ObjectType,
                            OBJECT_NAME(p.major_id) AS ObjectName,
                            SCHEMA_NAME(o.schema_id) AS SchemaName
                        FROM sys.database_permissions p
                        LEFT JOIN sys.objects o ON p.major_id = o.object_id
                        INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
                        WHERE dp.name = @UserName
                        AND p.class_desc = 'OBJECT_OR_COLUMN'";

                    using var permCommand = new SqlCommand(permQuery, connection);
                    permCommand.Parameters.AddWithValue("@LoginName", loginName);
                    permCommand.Parameters.AddWithValue("@DatabaseName", db);
                    permCommand.Parameters.AddWithValue("@UserName", loginName);

                    using var permReader = await permCommand.ExecuteReaderAsync();
                    while (await permReader.ReadAsync())
                    {
                        permissions.Add(new DatabasePermission
                        {
                            PrincipalName = permReader.GetString(0),
                            DatabaseName = permReader.GetString(1),
                            PermissionType = permReader.GetString(2),
                            PermissionState = permReader.GetString(3),
                            ObjectType = permReader.IsDBNull(4) ? "" : permReader.GetString(4),
                            ObjectName = permReader.IsDBNull(5) ? null : permReader.GetString(5),
                            SchemaName = permReader.IsDBNull(6) ? null : permReader.GetString(6)
                        });
                    }
                }
                catch
                {
                    // 忽略无权限访问的数据库
                }
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取账号权限失败: {ex.Message}", ex);
        }

        return permissions;
    }

    /// <summary>
    /// 授予数据库权限
    /// </summary>
    public async Task<bool> GrantPermissionAsync(GrantPermissionRequest request)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 切换到目标数据库
            await connection.ChangeDatabaseAsync(request.DatabaseName);

            // 首先检查用户是否存在，不存在则创建
            var checkUserSql = @"
                SELECT COUNT(1) 
                FROM sys.database_principals 
                WHERE name = @LoginName AND type = 'S'";

            using (var checkCommand = new SqlCommand(checkUserSql, connection))
            {
                checkCommand.Parameters.AddWithValue("@LoginName", request.LoginName);
                var userExists = (int)await checkCommand.ExecuteScalarAsync() > 0;

                if (!userExists)
                {
                    // 创建数据库用户
                    var createUserSql = $"CREATE USER [{request.LoginName}] FOR LOGIN [{request.LoginName}]";
                    using var createUserCommand = new SqlCommand(createUserSql, connection);
                    await createUserCommand.ExecuteNonQueryAsync();
                }
            }

            // 根据权限级别授予权限
            var grantSql = request.PermissionLevel switch
            {
                "ReadOnly" => $@"
                    ALTER ROLE db_datareader ADD MEMBER [{request.LoginName}]",

                "ReadWrite" => $@"
                    ALTER ROLE db_datareader ADD MEMBER [{request.LoginName}];
                    ALTER ROLE db_datawriter ADD MEMBER [{request.LoginName}]",

                "DDLAdmin" => $@"
                    ALTER ROLE db_datareader ADD MEMBER [{request.LoginName}];
                    ALTER ROLE db_datawriter ADD MEMBER [{request.LoginName}];
                    ALTER ROLE db_ddladmin ADD MEMBER [{request.LoginName}]",

                "Owner" => $@"
                    ALTER ROLE db_owner ADD MEMBER [{request.LoginName}]",

                _ => throw new ArgumentException("不支持的权限级别")
            };

            using var grantCommand = new SqlCommand(grantSql, connection);
            await grantCommand.ExecuteNonQueryAsync();

            // 如果有自定义权限，授予自定义权限
            if (request.CustomPermissions != null && request.CustomPermissions.Count > 0)
            {
                foreach (var permission in request.CustomPermissions)
                {
                    var customGrantSql = $"GRANT {permission} TO [{request.LoginName}]";
                    using var customCommand = new SqlCommand(customGrantSql, connection);
                    await customCommand.ExecuteNonQueryAsync();
                }
            }

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"授予权限失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 撤销数据库权限
    /// </summary>
    public async Task<bool> RevokePermissionAsync(RevokePermissionRequest request)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            // 切换到目标数据库
            await connection.ChangeDatabaseAsync(request.DatabaseName);

            // 移除所有角色成员关系
            var revokeSql = $@"
                -- 移除所有角色成员关系
                DECLARE @role_name sysname
                DECLARE role_cursor CURSOR FOR
                SELECT r.name
                FROM sys.database_role_members rm
                INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
                INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
                WHERE m.name = @LoginName

                OPEN role_cursor
                FETCH NEXT FROM role_cursor INTO @role_name

                WHILE @@FETCH_STATUS = 0
                BEGIN
                    EXEC sp_droprolemember @role_name, @LoginName
                    FETCH NEXT FROM role_cursor INTO @role_name
                END

                CLOSE role_cursor
                DEALLOCATE role_cursor

                -- 撤销所有对象权限
                DECLARE @sql NVARCHAR(MAX)
                DECLARE perm_cursor CURSOR FOR
                SELECT 'REVOKE ' + permission_name + ' ON ' + 
                       QUOTENAME(OBJECT_SCHEMA_NAME(major_id)) + '.' + 
                       QUOTENAME(OBJECT_NAME(major_id)) + 
                       ' FROM [' + @LoginName + ']'
                FROM sys.database_permissions
                WHERE grantee_principal_id = USER_ID(@LoginName)
                AND class = 1

                OPEN perm_cursor
                FETCH NEXT FROM perm_cursor INTO @sql

                WHILE @@FETCH_STATUS = 0
                BEGIN
                    EXEC sp_executesql @sql
                    FETCH NEXT FROM perm_cursor INTO @sql
                END

                CLOSE perm_cursor
                DEALLOCATE perm_cursor";

            using var revokeCommand = new SqlCommand(revokeSql, connection);
            revokeCommand.Parameters.AddWithValue("@LoginName", request.LoginName);
            await revokeCommand.ExecuteNonQueryAsync();

            // 删除数据库用户
            try
            {
                var dropUserSql = $"DROP USER [{request.LoginName}]";
                using var dropCommand = new SqlCommand(dropUserSql, connection);
                await dropCommand.ExecuteNonQueryAsync();
            }
            catch
            {
                // 用户可能拥有对象，无法删除
            }

            return true;
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"撤销权限失败: {ex.Message}", ex);
        }
    }

    /// <summary>
    /// 获取数据库的所有用户权限
    /// </summary>
    public async Task<List<DatabasePermission>> GetDatabasePermissionsAsync(string databaseName)
    {
        var permissions = new List<DatabasePermission>();

        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            await connection.ChangeDatabaseAsync(databaseName);

            var query = @"
                SELECT 
                    dp.name AS PrincipalName,
                    @DatabaseName AS DatabaseName,
                    p.permission_name AS PermissionType,
                    p.state_desc AS PermissionState,
                    ISNULL(o.type_desc, 'DATABASE') AS ObjectType,
                    OBJECT_NAME(p.major_id) AS ObjectName,
                    SCHEMA_NAME(o.schema_id) AS SchemaName
                FROM sys.database_permissions p
                INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
                LEFT JOIN sys.objects o ON p.major_id = o.object_id
                WHERE dp.type = 'S' -- SQL 用户
                AND dp.name NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
                ORDER BY dp.name, p.permission_name";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@DatabaseName", databaseName);

            using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                permissions.Add(new DatabasePermission
                {
                    PrincipalName = reader.GetString(0),
                    DatabaseName = reader.GetString(1),
                    PermissionType = reader.GetString(2),
                    PermissionState = reader.GetString(3),
                    ObjectType = reader.GetString(4),
                    ObjectName = reader.IsDBNull(5) ? null : reader.GetString(5),
                    SchemaName = reader.IsDBNull(6) ? null : reader.GetString(6)
                });
            }
        }
        catch (SqlException ex)
        {
            throw new InvalidOperationException($"获取数据库权限失败: {ex.Message}", ex);
        }

        return permissions;
    }

    /// <summary>
    /// 检查账号是否有特定数据库的访问权限
    /// </summary>
    public async Task<bool> HasPermissionAsync(string loginName, string databaseName)
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            await connection.ChangeDatabaseAsync(databaseName);

            var query = @"
                SELECT COUNT(1) 
                FROM sys.database_principals 
                WHERE name = @LoginName AND type = 'S'";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@LoginName", loginName);

            var count = (int)await command.ExecuteScalarAsync();
            return count > 0;
        }
        catch
        {
            return false;
        }
    }

    #endregion
}